<?php

namespace MLM\Model\Facility;

class Storage extends \MLM\Model\Generic\Db\Storage {


    public function getVoucherCode($op) {

        $sql = "select * from p_pulsa where pulsa_operator = ?";
        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($op));

        return $res;
    }
    
    public function getVoucherByCode($code) {

        $sql = "select * from p_pulsa where pulsa_code = ?";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($code));

        return $res;

    }

    public function getOperatorList() {

        $sql = "select distinct pulsa_operator from p_pulsa";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute();

        return $res;
    }

    


    function getDaftarPembayaran($memberId, $limit, $start){

        // Get Total
        $sql = "select count(1) as total
                from personal_data_trans where member_id = '$memberId' and trans_type_id in (21, 22)";

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];

        $data = array($rows);


        $sql = "select *
                from personal_data_trans where member_id = '$memberId' and trans_type_id in (21, 22) order by trans_name ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }


    function getTrackingTransaction($accNum, $transType, $date1, $date2, $start, $limit) {

        $sql = "select count(1) as total, FORMAT(sum(amt1),2) as jumlah
                from v_transaction where is_display = 'Y' and is_external = 'Y'
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                ";

        if ($accNum != ""){
            $sql .= " and acc_num1 = '$accNum' ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_id = $transType ";
        }

        //echo $sql;

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];
        $total = $result['jumlah'];

        $data = array($rows, $total);


        $sql = "select trans_dtm, acc_num1,
                notes_txt as descr,
                external_param, external_resp
                from v_transaction where is_display = 'Y'
                and is_external = 'Y'
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($accNum != ""){
            $sql .= " and acc_num1 = '$accNum' ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_id = $transType ";
        }

        $sql .= " order by trans_dtm desc ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }
    
}
